﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using Portal.Web.Utility;
using Portal.Core;
using Portal.Systems.Model;
using Portal.Database.Service;

namespace Portal.Exam.Bussiness
{
    public class DoExamBll
    {
        string GetExamConnectionString(string SiteId)
        {
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            string ExamConnectionString=WebConfig.GetConfigFromXml(OSiteParam, "ExamConnectionString");
            return ExamConnectionString;
        }

        public static AccountTypeCls[] ReadingAccountTypeForUser(string SiteId, string UserId)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            IDatabaseService service = WebDatabaseService.CreateDBService(SiteId);
            string assetCode = WebSessionUtility.GetCurrentLoginUser().AssetCode;
            string dataLanguage = WebConfig.GetDataLanguage(oSiteParam);
            string query =

            " select distinct " +
            " tableAccountType.AccountType,tableAccountType.Description" +
            " from tableAccountType,tableUser , TableUserRole,TableRoleAccountType,tablerole " +
            " where" +
            " tableuser.userid=TableUserRole.frkuserid " +
            " and TableUserRole.frkroleid = tablerole.RoleId" +
            " and tablerole.RoleId = TableRoleAccountType.RoleId		" +
            " and TableRoleAccountType.AccountType = tableAccountType.AccountType	" +
            " and tableuser.userid = @UserId " +
            " group by " +

            " tableAccountType.AccountType ,tableAccountType.Description ";
            
            DataSet set = service.GetDataSet(null, query, new DbParam[] { new DbParam("UserId", UserId) });
            if (set.Tables[0].Rows.Count == 0)
            {
                return new AccountTypeCls[0];
            }
            AccountTypeCls[] clsArray = AccountTypeParser.ParseFromDataTable(set.Tables[0]);
            set.Clear();
            set.Dispose();
            return clsArray;
        }

 

 


        public void UpdateUseTime(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamRoomStudentId)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query =
                //"Update TableExamRoomStudent set UseTime=-2 where ExamCode=@ExamCode and UseTime is null;" +
                "Update TableExamRoomStudent set StartExam=getdate() where StartExam is null and ExamRoomStudentId=@ExamRoomStudentId;" +
                "Update TableExamRoomStudent set UseTime=datediff(minute,StartExam,GetDate()) where ExamRoomStudentId=@ExamRoomStudentId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamRoomStudentId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamRoomStudentId"].Value = ExamRoomStudentId;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }






        public void SetFinishExam(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamRoomStudentId)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }
            string Query = 
                           " update TableExamRoomStudent set FinishExam=1,UseTime=TotalExamTime where ExamRoomStudentId=@ExamRoomStudentId;" +
                           " update TableExamRoomStudent set DonePoint=(select IsNull(sum(MaxPoint),0) from TableExamQuestion where TableExamQuestion.CorrectAnswer=1 and frkExamRoomStudentId=@ExamRoomStudentId and [Type]<>'FreeText') where ExamRoomStudentId=@ExamRoomStudentId;" +
                           
                           //dvtien backup
                           //" update TableExamRoomStudent set DonePoint=(select IsNull(sum(MaxPoint),0) from TableExamQuestion where TableExamQuestion.CorrectAnswer=1 and ExamRoomStudentId=@ExamRoomStudentId and [Type]<>'LargeFreeText') where ExamRoomStudentId=@ExamRoomStudentId;" +
                           //end

                           //" update TableExamRoomStudent set TeacherDoneExam=1 where ExamRoomStudentId=@ExamRoomStudentId and " +
                           //"    (" +
                           //"            select count(*) from TableExamQuestion " +
                           //"                where frkExamRoomStudentId=ExamRoomStudentId and Type in ('FreeText','LargeFreeText')" +
                           //"    )=0; " +
                           " update TableExamRoomStudent set Mark=(select IsNull(sum(MaxPoint),0) from TableExamQuestion where TableExamQuestion.CorrectAnswer=1 and frkExamRoomStudentId=@ExamRoomStudentId and [Type]<>'FreeText') where ExamRoomStudentId=@ExamRoomStudentId;";

            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamRoomStudentId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamRoomStudentId"].Value = ExamRoomStudentId;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                //vn.edu.wru.dangky.IUDATA IUWs = new vn.edu.wru.dangky.IUDATA();
                //doan nay call ws
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                    //ExamPersonalCls oExamPersonalCls = new ExamPersonalBll().CreateModel(SiteId, ExamCode);
                    //IUWs.IUTACNGHIEP_InsertMark_ExamList("10004301!@#", oExamPersonalCls.MarkId, oExamPersonalCls.CourseId, oExamPersonalCls.StudentId, oExamPersonalCls.MarkTypeId, oExamPersonalCls.StudyTimes, oExamPersonalCls.MarkNumber, oExamPersonalCls.ExaminationNumber, oExamPersonalCls.Semester, oExamPersonalCls.OriginMark, oExamPersonalCls.Mark, oExamPersonalCls.SpecialMarkId, oExamPersonalCls.Reserve, oExamPersonalCls.ExamListDetailId, oExamPersonalCls.ExamMarkId);
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }


        public void StartExam(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamRoomStudentId)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = " update TableExamRoomStudent set StartExam=GetDate() where ExamRoomStudentId=@ExamRoomStudentId;";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamRoomStudentId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamRoomStudentId"].Value = ExamRoomStudentId;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }


        public void SetNeedReview(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamQuestionId,
            int    MarkedNeedReview)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = " Update TableExamQuestion set MarkedNeedReview=@MarkedNeedReview where ExamQuestionId=@ExamQuestionId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;

            TempSqlCommand.Parameters.Add("@ExamQuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamQuestionId"].Value = ExamQuestionId;

            TempSqlCommand.Parameters.Add("@MarkedNeedReview", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@MarkedNeedReview"].Value = MarkedNeedReview;


            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }


        public int GetRemainTime(string SiteId, string ExamRoomStudentId)
        {
            string Query = " select UseTime,TotalExamTime from TableExamRoomStudent where ExamRoomStudentId = '" + ExamRoomStudentId + "'";
            SqlCommand SqlCommand = new SqlCommand(Query, new SqlConnection(GetExamConnectionString(SiteId)));
            SqlCommand.Connection.Open();
            SqlCommand.CommandText = Query;

            DataTable dtResult = new DataTable();
            SqlDataAdapter sqlda = new SqlDataAdapter(SqlCommand);
            sqlda.Fill(dtResult);

            if (dtResult.Rows.Count == 0)
            {
                return 0;
            }
            int ut = (int)dtResult.Rows[0]["UseTime"];
            //ut = ut * 60;
            int TotalExamTime = (int)dtResult.Rows[0]["TotalExamTime"];
            int RemainTime = TotalExamTime - ut;
            return RemainTime;
        }



        public DataTable ReadingStudentExamList(
            string SiteId,
            string AssetCode,
            string StudentCode)
        {
            SqlConnection OSqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
            OSqlConnection.Open();

            string Query =
                //" select ExamRoomStudentId,ExamTitle=IsNull(ExamTitle,ExamSubject),ExamDescription=IsNull(ExamDescription,''),Duration=IsNull(Duration,0),ExamSegmentName,UseTime,StartExam from TableExamRoomStudent " +
                " select ExamRoomStudentId,ExamSubject,ExamDescription=IsNull(ExamDescription,''),Duration=IsNull(Duration,0),ExamSegmentName,UseTime,StartExam from TableExamRoomStudent " +
                "       join TableExamRoom on TableExamRoom.ExamRoomId=frkExamRoomId" +
                "       join TableExamSegment on TableExamSegment.ExamSegmentId=frkExamSegmentId" +
                "   where StudentCode=@StudentCode and OpenForExam=1 and HasGenQuestion=1 and FinishExam=0";
                
            SqlCommand OSqlCommand = OSqlConnection.CreateCommand();
            OSqlCommand.CommandText = Query;
            OSqlCommand.Parameters.Add("@StudentCode", SqlDbType.VarChar);
            OSqlCommand.Parameters["@StudentCode"].Value = StudentCode;
            SqlDataAdapter sqlda = new SqlDataAdapter(OSqlCommand);
            DataTable dtResult = new DataTable();
            sqlda.Fill(dtResult);
            return dtResult;
        }

    }
}
